﻿using log4net;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YunSpace.Application.PushHandle;
using YunSpace.Application.SystemManage;
using YunSpace.Core;
using YunSpace.Data;
using YunSpace.Domain.Entity.OrderFlowManage;
using YunSpace.Domain.Entity.SiteMgr;
using YunSpace.Domain.Entity.SystemManage;
using YunSpace.Domain.IRepository.OrderFlowManage;
using YunSpace.Domain.IRepository.SiteMgr;
using YunSpace.Domain.ViewModel;
using YunSpace.Domain.ViewModel.SiteMgr;
using YunSpace.Domain.ViewModel.System;
using YunSpace.Repository.OrderFlowManage;
using YunSpace.Repository.SiteMgr;

namespace YunSpace.Application.SiteMgr
{
    public class DemandInfoApp
    {
        private static readonly Log logger = LogFactory.GetLogger(typeof(DemandInfoApp));
        private SysAutoApp autoApp = new SysAutoApp();
        private ItemsDetailApp itemsDetailApp = new ItemsDetailApp();
        ISiteClientsRepository clientRepository = new SiteClientsRepository();
        ISiteClientsAllProjectsRepository projectsRepository = new SiteClientsAllProjectsRepository();
        ISiteClientsProjectByContactRepository contactRepository = new SiteClientsProjectByContactRepository();
        ISiteClientsProjectByDemandRepository demandRepository = new SiteClientsProjectByDemandRepository();
        ISiteClientsServiceMappingRepository serviceRepository = new SiteClientsServiceMappingRepository();
        ISiteClientsBrandsEntityRepository brandsRepository = new SiteClientsBrandsEntityRepository();
        IClientOrderRepository orderRepository = new ClientOrderRepository();

        public List<ItemsDetailActionModel> GetItemList(string enCode)
        {
            return itemsDetailApp.GetItemList(enCode);
        }

        public List<AreaModel> GetAllCitys()
        {
            List<AreaModel> citys = CacheFactory.Cache().Get("City") as List<AreaModel>;
            if (citys == null)
            {
                using (var db = new RepositoryBase())
                {
                    string strSql = @"select a.F_Id,a.F_ParentId,a.F_EnCode,a.F_FullName  from Sys_Area a where a.F_Layers=2";
                    citys = db.FindList<AreaModel>(strSql);
                    CacheFactory.Cache().Insert("City", citys);
                }
            }
            return citys;
        }

        public List<AreaModel> GetAllAreas()
        {
            List<AreaModel> citys = CacheFactory.Cache().Get("Area") as List<AreaModel>;
            if (citys == null)
            {
                using (var db = new RepositoryBase())
                {
                    string strSql = @"select a.F_Id,a.F_ParentId,a.F_EnCode,a.F_FullName  from Sys_Area a where a.F_Layers=3";
                    citys = db.FindList<AreaModel>(strSql);
                    CacheFactory.Cache().Insert("Area", citys);
                }
            }
            return citys;
        }

        public List<ClientDemandAllotActionModel> GetDemandAllotList(Pagination pagination)
        {
            List<ClientDemandAllotActionModel> result = new List<ClientDemandAllotActionModel>();
            using (var db = new RepositoryBase())
            {
                string strSql = @"select a.F_Id,a.F_RealName,b.F_LastTime,b.F_ShareNum,c.F_MineNeedNum from  Sys_User a 
                                left join (select scd.F_FlowUserId,count(1)F_ShareNum,max(scd.F_CreatorTime) F_LastTime from (select * from Site_Client_DemandAllot n where n.F_CreatorTime<DATE_ADD(NOW(),INTERVAL 1 MONTH)) scd group by scd.F_FlowUserId) b on b.F_FlowUserId=a.F_Id
                                left join (select scp.F_CreatorUserId,count(1)F_MineNeedNum from  Site_Clients_ProjectByDemand scp where scp.F_Source=1 group by scp.F_CreatorUserId) c on a.F_Id=c.F_CreatorUserId
                                inner join Sys_Role d on a.F_RoleId=d.F_Id
                                where a.F_DepartmentId in ('5c60c965-8404-452e-8628-b00ee3714e95','01068646-a03a-483e-95fd-41c601552b75','32bc6d4a-3da0-4583-b267-0d751d94bde3','53cca542-a693-4a51-b66e-1ce5daa59b21') 
                                and a.F_RoleId in ('bac7c8d3-523a-483a-ac50-50f178df9427','8d092152-9885-4d53-9fbd-8791191e5098','3b3771c6-e7f4-4fba-aae0-9dd5f0fae3cf','ac595b87-77fe-4e56-8e50-4cea28e11f7c') 
                                and a.F_EnabledMark=1 and a.F_Id not in('95dbacb1-0d5c-11e8-bc3f-00163e00446b','9f2ec080-7d0f-4fe2-90ab-8b09a8302aba')";
                DbParameter[] parameter =
                {
                };
                result = db.FindList<ClientDemandAllotActionModel>(strSql, parameter, pagination);
            }
            return result;
        }

        public ClientTelDemandEditActionModel GetMemandEntity(string F_Id)
        {
            ClientTelDemandEditActionModel result = new ClientTelDemandEditActionModel();
            using (var db = new RepositoryBase())
            {
                string strSql = @"select a.F_Id,c.F_ClientType,c.F_Id as F_ClientId,a.F_Source,j.F_Id F_BrandId,j.F_BrandName,c.F_ClientName,c.F_CompanyType,c.F_ClientLevel,
                                c.F_CityId,c.F_AreaId,c.F_Address,c.F_IndustryId,b.F_Id ProjectId,
                                b.F_ProjectName,e.F_Contact,e.F_DepartmentId,e.F_PositionId,e.F_Sex,
                                e.F_Phone,e.F_Telephone,e.F_Mail,a.F_ActivityTheme,a.F_ActivityType,DATE_FORMAT(a.F_BuildDate,'%Y-%m-%d') F_BuildDate,
                                a.F_DemandCash,DATE_FORMAT(a.F_ActivityStartDate,'%Y-%m-%d') F_ActivityStartDate,DATE_FORMAT(a.F_ActivityEndDate,'%Y-%m-%d') F_ActivityEndDate,a.F_ActivityCityId,a.F_SiteType,
                                a.F_SiteArea,a.F_Height,a.F_Note,a.F_Mark,a.F_State,
                                k.F_Id MappingId,k.F_ClientName MappingName,a.F_IndustryId F_ZkIndustryId,a.F_PersionNumber,a.F_CloseReason,a.F_Intention
                                 from Site_Clients_ProjectByDemand a
                                inner join Site_Clients c on a.F_ClientId=c.F_Id  
								left join Site_Clients_AllProjectsMapping sca on sca.F_DemandId=a.F_Id
								left join Site_Clients_AllProjects b on sca.F_ProjectId=b.F_Id 
								left join Site_Clients_ContactMapping scc on scc.F_DemandId=a.F_Id
								left join Site_Clients_ProjectByContact e on e.F_Id=scc.F_ContactId 
                                left join Site_Clients_BrandsMapping h on a.F_Id=h.F_DemandId
								left join Site_Clients_Brands j on h.F_BrandId=j.F_Id
                                left join Site_Client_Order p on p.F_DemandId=a.F_Id
                                left join (select f.F_ServiceId,g.F_Id,g.F_ClientName,g.F_IndustryId from Site_Clients_ServiceMapping f 
								inner join Site_Clients g on f.F_ClientId=g.F_Id group by f.F_ServiceId,g.F_Id,g.F_ClientName,g.F_IndustryId) k 
								on c.F_Id=k.F_ServiceId
                                where 1=1 and a.F_Id=@F_Id";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_Id", F_Id),
                };
                result = db.FindList<ClientTelDemandEditActionModel>(strSql, parameter).FirstOrDefault();
            }
            return result;
        }

        public List<ClientOrderHistoryActionModel> GetClientOrderHistoryList(Pagination pagination, string F_ClientId, string F_Id)
        {
            List<ClientOrderHistoryActionModel> clientList = new List<ClientOrderHistoryActionModel>();
            using (var db = new RepositoryBase().BeginTrans())
            {
                var strSql = @"select a.F_Id,a.F_ClientName,b.F_ProjectName,d.F_ActivityTheme,d.F_DemandCash,d.F_ActivityStartDate,d.F_ActivityEndDate,
                                d.F_ActivityCityId,d.F_State,c.F_Contact,c.F_Phone,e.F_State orderState,d.F_CreatorTime,d.F_Id DemandId,
                                e.F_OrderState,e.F_OrderStateProcess
                                from Site_Clients a
                                inner join Site_Clients_ProjectByDemand d on d.F_ClientId=a.F_Id
                                left join Site_Clients_AllProjectsMapping sca on sca.F_DemandId=d.F_Id
                                left join Site_Clients_AllProjects b on b.F_Id=sca.F_ProjectId
                                left join Site_Clients_ContactMapping scc on scc.F_DemandId=d.F_Id
                                left join Site_Clients_ProjectByContact c on c.F_Id=scc.F_ContactId    
                                left join Site_Client_Order e on e.F_DemandId=d.F_Id where a.F_Id=@F_ClientId and d.F_Id not in(@F_Id)";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_ClientId", F_ClientId),
                    new MySqlParameter("@F_Id", F_Id),
                };
                clientList = db.FindList<ClientOrderHistoryActionModel>(strSql, parameter, pagination);
            }
            return clientList;
        }

        public List<ClientDemandOrderActionModel> GetDemandList(Pagination pagination, string queryJson)
        {
            List<ClientDemandOrderActionModel> result = new List<ClientDemandOrderActionModel>();
            using (var db = new RepositoryBase())
            {
                string strSql = @"select a.F_Id,f.F_Id contactId,b.F_Id F_ProjectId,d.F_Id F_OrderId,scb.F_BrandId,a.F_Source,c.F_Id F_ClientId,br.F_BrandName,c.F_ClientName,b.F_ProjectName,a.F_ActivityTheme,a.F_ActivityType,a.F_DemandCash,
                                DATE_FORMAT(a.F_BuildDate,'%Y-%m-%d') F_BuildDate,DATE_FORMAT(a.F_ActivityStartDate,'%Y-%m-%d') F_ActivityStartDate,DATE_FORMAT(a.F_ActivityEndDate,'%Y-%m-%d') F_ActivityEndDate,a.F_ActivityCityId,a.F_SiteType,a.F_SiteArea,a.F_Height,a.F_State,a.F_State F_State1,
                                e.F_RealName,case when d.F_OrderState is null then -1 when d.F_OrderState is not null then d.F_OrderState end F_OrderStateName,
							    case when d.F_OrderState is null then -1 when d.F_OrderState is not null then d.F_OrderState end F_OrderState,a.F_CreatorTime,a.F_PersionNumber,f.F_Contact, a.F_Referral
                                from Site_Clients_ProjectByDemand a
                                inner join Site_Clients c on a.F_ClientId=c.F_Id
                                left join Site_Clients_AllProjectsMapping sca on sca.F_DemandId=a.F_Id
                                left join Site_Clients_AllProjects b on b.F_Id=sca.F_ProjectId                                                              
                                left join Site_Clients_ContactMapping scc on scc.F_DemandId=a.F_Id
                                left join Site_Clients_ProjectByContact f on f.F_Id=scc.F_ContactId 
                                left join Site_Clients_BrandsMapping scb on scb.F_DemandId=a.F_Id 
                                left join Site_Clients_Brands br on br.F_Id=scb.F_BrandId
                                left join Site_Client_Order d on d.F_DemandId=a.F_Id
                                left join Sys_User e on a.F_CreatorUserId=e.F_Id  where 1=1 {0} {1} {2} {3} {4} {5} {6} {7} {8} {9}  {10} order by a.F_CreatorTime desc,d.F_OrderState desc";
                var queryParam = queryJson.ToJObject();
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_ActivityCityId", queryParam["F_ActivityCityId"]),
                    new MySqlParameter("@F_ActivityType", queryParam["F_ActivityType"]),
                    new MySqlParameter("@F_DemandCash", queryParam["F_DemandCash"]),
                    new MySqlParameter("@F_ClientName", queryParam["F_ClientName"]),
                    new MySqlParameter("@F_ActivityStartDate", queryParam["F_ActivityStartDate"]),
                    new MySqlParameter("@F_CreatorTime", queryParam["F_CreatorTime"]+" 00:00:00"),
                    new MySqlParameter("@F_Source", queryParam["F_Source"]),
                    new MySqlParameter("@F_State", queryParam["F_State"]),
                    new MySqlParameter("@F_CreatorTime2", queryParam["F_CreatorTime2"]+" 23:59:59"),
                };

                ///获取用户权限
                var LoginInfo = OperatorProvider.Provider.GetCurrent();
                RoleApp role = new RoleApp();
                RoleEntity roleEntity = role.GetForm(LoginInfo.RoleId);
                OrganizeApp organize = new OrganizeApp();
                OrganizeEntity organizeEntity = organize.GetForm(LoginInfo.DepartmentId);

                string F_GroupId = "";
                if (roleEntity.F_EnCode == "1006" || roleEntity.F_EnCode == "1012")
                {
                    F_GroupId += " and e.F_GroupId='" + LoginInfo.F_GroupId + "'";
                }
                else if (roleEntity.F_EnCode == "1004")
                {
                    F_GroupId += " and e.F_GroupId='" + LoginInfo.F_GroupId + "' and e.F_Id='" + LoginInfo.F_GroupId + "'";
                }

                strSql = string.Format(strSql,
                    queryParam["F_ActivityCityId"].IsEmpty() ? "" : " and a.F_ActivityCityId=@F_ActivityCityId",
                    queryParam["F_ActivityType"].IsEmpty() ? "" : " and a.F_ActivityType=@F_ActivityType",
                    queryParam["F_DemandCash"].IsEmpty() ? "" : " and a.F_DemandCash>=@F_DemandCash",
                    queryParam["F_ClientName"].IsEmpty() ? "" : " and c.F_Id=@F_ClientName",
                    queryParam["F_ActivityStartDate"].IsEmpty() ? "" : " and a.F_ActivityStartDate>=@F_ActivityStartDate",
                    queryParam["F_CreatorTime"].IsEmpty() ? "" : " and a.F_CreatorTime>=@F_CreatorTime",
                    queryParam["F_Source"].IsEmpty() ? "" : queryParam["F_Source"].ToString() == "-1" ? " and a.F_Source in(0,2)" : " and a.F_Source=@F_Source",
                    queryParam["F_State"].IsEmpty() ? "" : queryParam["F_State"].ToString() == "-1" ? "" : " and a.F_State=@F_State",
                    F_GroupId,
                    queryParam["F_Phone"].IsEmpty() ? "" : " and f.F_Phone like '%" + queryParam["F_Phone"] + "%'",
                    queryParam["F_CreatorTime2"].IsEmpty() ? "" : " and a.F_CreatorTime<=@F_CreatorTime2"

                    );
                result = db.FindList<ClientDemandOrderActionModel>(strSql, parameter, pagination);
            }
            return result;
        }

        public List<ClientDemandOrderActionModel> GetGridDataByUserId(Pagination pagination, string queryJson, string F_CreatorUserId)
        {
            List<ClientDemandOrderActionModel> result = new List<ClientDemandOrderActionModel>();
            using (var db = new RepositoryBase())
            {
                string strSql = @"select a.F_Id,f.F_Id contactId,b.F_Id F_ProjectId,d.F_Id F_OrderId,scb.F_BrandId,a.F_Source,c.F_Id F_ClientId,br.F_BrandName,c.F_ClientName,b.F_ProjectName,a.F_ActivityTheme,a.F_ActivityType,a.F_DemandCash,
                                DATE_FORMAT(a.F_ActivityStartDate,'%Y-%m-%d') F_ActivityStartDate,DATE_FORMAT(a.F_ActivityEndDate,'%Y-%m-%d') F_ActivityEndDate,a.F_ActivityCityId,a.F_SiteType,a.F_SiteArea,a.F_Height,a.F_State,
                                e.F_RealName,case when d.F_OrderState is null then -1 when d.F_OrderState is not null then d.F_OrderState end F_OrderStateName,
							    case when d.F_OrderState is null then -1 when d.F_OrderState is not null then d.F_OrderState end F_OrderState,a.F_CreatorTime,a.F_PersionNumber
                                from Site_Clients_ProjectByDemand a
                                inner join Site_Clients c on a.F_ClientId=c.F_Id
                                left join Site_Clients_AllProjectsMapping sca on sca.F_DemandId=a.F_Id
                                left join Site_Clients_AllProjects b on b.F_Id=sca.F_ProjectId                                                              
                                left join Site_Clients_ContactMapping scc on scc.F_DemandId=a.F_Id
                                left join Site_Clients_ProjectByContact f on f.F_Id=scc.F_ContactId 
                                left join Site_Clients_BrandsMapping scb on scb.F_DemandId=a.F_Id 
                                left join Site_Clients_Brands br on br.F_Id=scb.F_BrandId
                                left join Site_Client_Order d on d.F_DemandId=a.F_Id
                                left join Sys_User e on d.F_FlowUserId=e.F_Id where 1=1 and d.f_state is null {0} {1} {2} {3} {4} {5} {6} {7} {8} order by a.F_CreatorTime desc";
                var queryParam = queryJson.ToJObject();
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_ActivityCityId", queryParam["F_ActivityCityId"]),
                    new MySqlParameter("@F_ActivityType", queryParam["F_ActivityType"]),
                    new MySqlParameter("@F_DemandCash", queryParam["F_DemandCash"]),
                    new MySqlParameter("@F_ClientName", queryParam["F_ClientName"]),
                    new MySqlParameter("@F_ActivityStartDate", queryParam["F_ActivityStartDate"]),
                    new MySqlParameter("@F_CreatorTime", queryParam["F_CreatorTime"]),
                    new MySqlParameter("@F_State", queryParam["F_State"]),
                    new MySqlParameter("@F_CreatorUserId", F_CreatorUserId),
                    new MySqlParameter("@F_CreatorTime2", queryParam["F_CreatorTime2"]),

                };
                strSql = string.Format(strSql,
                    queryParam["F_ActivityCityId"].IsEmpty() ? "" : " and a.F_ActivityCityId=@F_ActivityCityId",
                    queryParam["F_ActivityType"].IsEmpty() ? "" : " and a.F_ActivityType=@F_ActivityType",
                    queryParam["F_DemandCash"].IsEmpty() ? "" : " and a.F_DemandCash>=@F_DemandCash",
                    queryParam["F_ClientName"].IsEmpty() ? "" : " and c.F_Id=@F_ClientName",
                    queryParam["F_ActivityStartDate"].IsEmpty() ? "" : " and a.F_ActivityStartDate>=@F_ActivityStartDate",
                    queryParam["F_CreatorTime"].IsEmpty() ? "" : " and a.F_CreatorTime>=@F_CreatorTime",
                    queryParam["F_State"].IsEmpty() ? "" : queryParam["F_State"].ToString() == "-1" ? "" : " and a.F_State=@F_State",
                    F_CreatorUserId.IsEmpty() ? "" : " and d.F_FlowUserId=@F_CreatorUserId",
                    queryParam["F_CreatorTime2"].IsEmpty() ? "" : " and a.F_CreatorTime<=@F_CreatorTime2"

                    );
                result = db.FindList<ClientDemandOrderActionModel>(strSql, parameter, pagination);
            }
            return result;
        }

        /// <summary>
        /// 获取所有的项目组信息
        /// </summary>
        /// <returns></returns>
        public List<SiteClientsAllProjectsEntity> GetClientsAllProjects()
        {
            List<SiteClientsAllProjectsEntity> projects = CacheFactory.Cache().Get("XMZXX") as List<SiteClientsAllProjectsEntity>;
            if (projects == null)
            {
                using (var db = new RepositoryBase().BeginTrans())
                {
                    var strSql = @"select *  from Site_Clients_AllProjects a where a.F_ProjectName is not null";
                    projects = db.FindList<SiteClientsAllProjectsEntity>(strSql);
                    CacheFactory.Cache().Insert("XMZXX", projects);
                }
            }
            return projects;
        }

        /// <summary>
        /// 查询是否存在客户和服务商映射关系
        /// </summary>
        /// <param name="F_ClientId"></param>
        /// <param name="F_ServiceId"></param>
        /// <returns></returns>
        public SiteClientsServiceMappingEntity GetFindServiceMapping(string F_ClientId, string F_ServiceId)
        {
            SiteClientsServiceMappingEntity serviceEntity = new SiteClientsServiceMappingEntity();
            using (var db = new RepositoryBase())
            {
                var strSql = @"select * from Site_Clients_ServiceMapping a where a.F_ClientId=@F_ClientId and a.F_ServiceId=@F_ServiceId";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_ClientId", F_ClientId),
                    new MySqlParameter("@F_ServiceId", F_ServiceId)
                };
                serviceEntity = db.FindList<SiteClientsServiceMappingEntity>(strSql, parameter).FirstOrDefault();
            }
            return serviceEntity;
        }

        /// <summary>
        /// 通过项目组编号、联系人、联系电话获取其信息
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="contract"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public SiteClientsProjectByContactEntity GetContactEntity(string projectId, string contract, string phone)
        {
            var result = new SiteClientsProjectByContactEntity();
            using (var db = new RepositoryBase())
            {
                string strSql = @"select * from Site_Clients_ProjectByContact where F_ProjectId=@projectId and F_Contact=@contract and F_Phone=@phone";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@projectId", projectId),
                    new MySqlParameter("@contract", contract),
                    new MySqlParameter("@phone", phone),
                };
                result = db.FindList<SiteClientsProjectByContactEntity>(strSql, parameter).FirstOrDefault();
            }
            return result;
        }

        public SiteClientDemandAllot FindEntity(string F_DemandId)
        {
            SiteClientDemandAllot entity = new SiteClientDemandAllot();
            using (var db = new RepositoryBase())
            {
                string strSql = @"select * from Site_Client_DemandAllot a where a.F_DemandId='" + F_DemandId + "'";
                entity = db.FindList<SiteClientDemandAllot>(strSql).FirstOrDefault();
            }
            return entity;
        }

        public void SubmitAllotForm(string F_Id, string F_UserId)
        {
            SiteClientsProjectByDemandEntity demandEntity;
            using (var db = new RepositoryBase().BeginTrans())
            {
                SiteClientDemandAllot allot = FindEntity(F_Id);
                if (allot == null)
                {
                    allot = new SiteClientDemandAllot();
                    allot.Create();
                    allot.F_DemandId = F_Id;
                    allot.F_FlowUserId = F_UserId;
                    db.Insert<SiteClientDemandAllot>(allot);
                }
                else
                {
                    allot.F_FlowUserId = F_UserId;
                    db.Update<SiteClientDemandAllot>(allot);
                }

                ClientOrderEntity orderEntity = new ClientOrderEntity();
                string strSql = "select * from Site_Client_Order a where a.F_DemandId=@F_DemandId";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_DemandId", F_Id)
                };
                demandEntity = demandRepository.FindEntity(F_Id);
                demandEntity.F_State = 1;
                demandEntity.F_CreatorUserId = F_UserId;
                db.Update<SiteClientsProjectByDemandEntity>(demandEntity);

                //SiteClientsEntity client = clientRepository.FindEntity(demandEntity.F_ClientId);
                //client.F_CreatorUserId = F_UserId;
                //db.Update<SiteClientsEntity>(client);

                orderEntity = db.FindList<ClientOrderEntity>(strSql, parameter).FirstOrDefault();
                orderEntity.F_FlowUserId = F_UserId;
                orderEntity.F_OrderState = 1;
                orderEntity.F_OrderStateProcess = -1;
                orderEntity.F_WorkFormId = "lc1001";
                orderEntity.F_IsRead = 0;
                db.Update<ClientOrderEntity>(orderEntity);
                db.Commit();
            }
            PushHandleManage pushApp = new PushHandleManage();
            pushApp.ShareSingle(F_UserId, demandEntity);
        }

        public List<SiteClientsBrandsEntity> GetBrands()
        {
            List<SiteClientsBrandsEntity> brandsList = CacheFactory.Cache().Get("PPXX") as List<SiteClientsBrandsEntity>;
            if (brandsList == null)
            {
                using (var db = new RepositoryBase().BeginTrans())
                {
                    var strSql = @"select *  from Site_Clients_Brands a where a.F_BrandName is not null";
                    brandsList = db.FindList<SiteClientsBrandsEntity>(strSql);
                    CacheFactory.Cache().Insert("PPXX", brandsList);
                }
            }
            return brandsList;
        }

        /// <summary>
        /// 获取所有的联系人信息
        /// </summary>
        /// <returns></returns>
        public List<SiteClientsProjectByContactEntity> GetClientsContacts()
        {
            List<SiteClientsProjectByContactEntity> contacts = CacheFactory.Cache().Get("LXRXX") as List<SiteClientsProjectByContactEntity>;
            if (contacts == null)
            {
                using (var db = new RepositoryBase().BeginTrans())
                {
                    var strSql = @"select *  from Site_Clients_ProjectByContact a where a.F_Contact is not null";
                    contacts = db.FindList<SiteClientsProjectByContactEntity>(strSql);
                    CacheFactory.Cache().Insert("LXRXX", contacts);
                }
            }
            return contacts;
        }

        /// <summary>
        /// 获取所有的客户信息
        /// </summary>
        /// <returns></returns>
        public List<SiteClientsEntity> GetClients()
        {
            List<SiteClientsEntity> clientList = CacheFactory.Cache().Get("KHXX") as List<SiteClientsEntity>;
            if (clientList == null)
            {
                using (var db = new RepositoryBase().BeginTrans())
                {
                    var strSql = @"select *  from Site_Clients where F_ClientName is not null";
                    clientList = db.FindList<SiteClientsEntity>(strSql);
                    CacheFactory.Cache().Insert("KHXX", clientList);
                }
            }
            return clientList;
        }

        public SiteClientsBrandsMappingEntity GetFindBrandsMappingEntity(string F_BrandId, string F_DemandId)
        {
            SiteClientsBrandsMappingEntity result = new SiteClientsBrandsMappingEntity();
            using (var db = new RepositoryBase())
            {
                var strSql = @"select * from Site_Clients_BrandsMapping a where a.F_BrandId=@F_BrandId and a.F_DemandId=@F_DemandId";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_BrandId", F_BrandId),
                    new MySqlParameter("@F_DemandId", F_DemandId)
                };
                result = db.FindList<SiteClientsBrandsMappingEntity>(strSql, parameter).FirstOrDefault();
            }
            return result;
        }

        /// <summary>
        /// 获取需求联系人关系
        /// </summary>
        /// <param name="F_ContactId"></param>
        /// <param name="F_DemandId"></param>
        /// <returns></returns>
        public SiteClientsContactMappingEntity GetSiteClientsContactMapping(string F_ContactId, string F_DemandId)
        {
            SiteClientsContactMappingEntity result = new SiteClientsContactMappingEntity();
            using (var db = new RepositoryBase())
            {
                var strSql = @"select * from Site_Clients_ContactMapping a where a.F_ContactId=@F_ContactId and a.F_DemandId=@F_DemandId";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_ContactId", F_ContactId),
                    new MySqlParameter("@F_DemandId", F_DemandId)
                };
                result = db.FindList<SiteClientsContactMappingEntity>(strSql, parameter).FirstOrDefault();
            }
            return result;
        }

  
        /// <summary>
        /// 重新发布需求->将old 需求联系人ID插入新的联系人关系映射表
        /// </summary>
        ///
        public string SubmitOldDemandConvertSiteClientsContactMappingEntity(string Old_DemandId, string New_DemandId) {
            // 添加客户联系人与需求映射表

            var result = "";
            using (var db = new RepositoryBase())
            {
               
                var oldContactMappingEntity = db.IQueryable<SiteClientsContactMappingEntity>(c => c.F_DemandId == Old_DemandId).FirstOrDefault();
                // 判断是否存在关系
                if (oldContactMappingEntity !=null)
                {
                    var clientsContactMappingEntity = new SiteClientsContactMappingEntity
                    {
                        F_ContactId = oldContactMappingEntity.F_ContactId,
                        F_DemandId = New_DemandId
                    };

                    clientsContactMappingEntity.Create();
                    db.Insert<SiteClientsContactMappingEntity>(clientsContactMappingEntity);

                    return clientsContactMappingEntity.F_Id;

                }
            }
            return result;

        }

        public SiteClientsAllProjectsMappingEntity GetSiteClientsAllProjectsMapping(string projectId, string F_DemandId)
        {
            SiteClientsAllProjectsMappingEntity result = new SiteClientsAllProjectsMappingEntity();
            using (var db = new RepositoryBase())
            {
                var strSql = @"select * from Site_Clients_AllProjectsMapping a where a.F_ProjectId=@F_ProjectId and a.F_DemandId=@F_DemandId";
                DbParameter[] parameter =
                {
                    new MySqlParameter("@F_ProjectId", projectId),
                    new MySqlParameter("@F_DemandId", F_DemandId)
                };
                result = db.FindList<SiteClientsAllProjectsMappingEntity>(strSql, parameter).FirstOrDefault();
            }
            return result;
        }

        public ClientOrderEntity SubmitForm(ClientTelDemandActionModel entity)
        {
            ClientOrderEntity order = null;
            try
            {
                using (var db = new RepositoryBase().BeginTrans())
                {
                    SiteClientsEntity clientEntity = null;
                    string strSql = @"select * from Site_Clients where f_id='" + entity.F_ClientId + "'";
                    clientEntity = db.FindList<SiteClientsEntity>(strSql).FirstOrDefault();
                    if (clientEntity != null)
                    {
                        clientEntity.F_ClientType = entity.F_ClientType;
                        clientEntity.F_CityId = entity.F_CityId;
                        clientEntity.F_AreaId = entity.F_AreaId;
                        clientEntity.F_Address = entity.F_Address;
                        clientEntity.F_IndustryId = entity.F_IndustryId;
                        clientEntity.F_ClientLevel = entity.F_ClientLevel;
                        clientEntity.F_ClientName = entity.F_ClientName;
                        db.Update<SiteClientsEntity>(clientEntity);
                    }
                    else
                    {
                        clientEntity = new SiteClientsEntity();
                        clientEntity.Create();
                        clientEntity.F_Id = entity.F_ClientId;
                        clientEntity.F_ClientType = entity.F_ClientType;
                        clientEntity.F_CityId = entity.F_CityId;
                        clientEntity.F_AreaId = entity.F_AreaId;
                        clientEntity.F_Address = entity.F_Address;
                        clientEntity.F_IndustryId = entity.F_IndustryId;
                        clientEntity.F_ClientLevel = entity.F_ClientLevel;
                        clientEntity.F_ClientName = entity.F_ClientName;
                        db.Insert<SiteClientsEntity>(clientEntity);
                    }
                    string strSql2 = @"select * from Site_Clients_ProjectByDemand where f_id='" + entity.F_Id + "'";
                    SiteClientsProjectByDemandEntity demandEntity = db.FindList<SiteClientsProjectByDemandEntity>(strSql2).FirstOrDefault();
                    if (demandEntity != null)
                    {
                        demandEntity.F_Source = entity.F_Source;
                        demandEntity.F_ActivityTheme = entity.F_ActivityTheme;
                        demandEntity.F_ActivityType = entity.F_ActivityType;
                        demandEntity.F_DemandCash = entity.F_DemandCash;
                        demandEntity.F_ActivityStartDate = entity.F_ActivityStartDate;

                        demandEntity.F_ActivityEndDate = entity.F_ActivityEndDate;
                        demandEntity.F_ActivityCityId = entity.F_ActivityCityId;
                        demandEntity.F_SiteType = entity.F_SiteType;
                        demandEntity.F_SiteArea = entity.F_SiteArea;
                        demandEntity.F_Height = entity.F_Height;

                        demandEntity.F_PersionNumber = entity.F_PersionNumber;
                        demandEntity.F_Note = entity.F_Note;
                        demandEntity.F_State = entity.F_State;
                        demandEntity.F_Mark = entity.F_Mark;
                        demandEntity.F_ClientId = entity.F_ClientId;
                        demandEntity.F_CloseReason = entity.F_CloseReason;
                        demandEntity.F_BuildDate = entity.F_BuildDate;
                        demandEntity.F_IndustryId = entity.F_ZkIndustryId;
                        demandEntity.F_Intention = entity.F_Intention;
                        db.Update<SiteClientsProjectByDemandEntity>(demandEntity);
                    }

                    // 直客
                    //string strSql3 = @"select * from Site_Clients where f_id='" + entity.F_MappingId + "'";
                    SiteClientsEntity mappingClient = clientRepository.FindEntity(entity.F_MappingId);
                    if (mappingClient == null)
                    {
                        mappingClient = new SiteClientsEntity();
                        mappingClient.Create();
                        mappingClient.F_ClientName = entity.F_MappingName;
                        db.Insert<SiteClientsEntity>(mappingClient);

                        SiteClientsServiceMappingEntity mapping = new SiteClientsServiceMappingEntity();
                        mapping.Create();
                        mapping.F_ClientId = mappingClient.F_Id;
                        mapping.F_ServiceId = entity.F_ClientId;
                        db.Insert<SiteClientsServiceMappingEntity>(mapping);
                    }
                    else
                    {
                        if (entity.F_MappingName != null)
                        {
                            mappingClient.F_ClientName = entity.F_MappingName;
                            db.Update<SiteClientsEntity>(mappingClient);
                        }

                        SiteClientsServiceMappingEntity mapping = GetFindServiceMapping(entity.F_MappingId, entity.F_ClientId);
                        if (mapping == null)
                        {
                            mapping = new SiteClientsServiceMappingEntity();
                            mapping.Create();
                            mapping.F_ClientId = mappingClient.F_Id;
                            mapping.F_ServiceId = entity.F_ClientId;
                            db.Insert<SiteClientsServiceMappingEntity>(mapping);
                        }
                    }

                    //品牌组信息
                    //string strSql4 = @"select * from Site_Clients_Brands where f_id='" + entity.F_BrandId + "'";
                    SiteClientsBrandsEntity brands = brandsRepository.FindEntity(entity.F_BrandId);
                    if (entity.F_BrandId != null && entity.F_BrandId.Trim() != "null")
                    {
                        //品牌组信息                    
                        if (brands == null)
                        {
                            brands = new SiteClientsBrandsEntity();
                            brands.Create();
                            brands.F_ClientId = entity.F_ClientId;
                            brands.F_BrandName = entity.F_BrandId;
                            db.Insert<SiteClientsBrandsEntity>(brands);
                            List<SiteClientsBrandsEntity> brandsList = CacheFactory.Cache().Get("PPXX") as List<SiteClientsBrandsEntity>;
                            if (brandsList == null)
                            {
                                brandsList = new List<SiteClientsBrandsEntity>();
                            }
                            brandsList.Add(brands);
                            CacheFactory.Cache().Insert("PPXX", brandsList);

                            SiteClientsBrandsMappingEntity brandsMapping = new SiteClientsBrandsMappingEntity();
                            brandsMapping.Create();
                            brandsMapping.F_DemandId = demandEntity.F_Id;
                            brandsMapping.F_BrandId = brands.F_Id;
                            db.Insert<SiteClientsBrandsMappingEntity>(brandsMapping);
                        }
                        else
                        {
                            brands.F_BrandName = entity.F_BrandName;
                            db.Update<SiteClientsBrandsEntity>(brands);

                            SiteClientsBrandsMappingEntity brandsMapping = GetFindBrandsMappingEntity(entity.F_BrandId, demandEntity.F_Id);
                            if (brandsMapping == null)
                            {
                                brandsMapping = new SiteClientsBrandsMappingEntity();
                                brandsMapping.Create();
                                brandsMapping.F_DemandId = demandEntity.F_Id;
                                brandsMapping.F_BrandId = brands.F_Id;
                                db.Insert<SiteClientsBrandsMappingEntity>(brandsMapping);
                            }
                        }
                    }

                    //项目组信息
                    //string strSql5 = @"select * from Site_Clients_AllProjects where f_id='" + entity.F_ProjectId + "'";
                    SiteClientsAllProjectsEntity projects = projectsRepository.FindEntity(entity.F_ProjectId);
                    if (entity.F_ProjectId != null && entity.F_ProjectId.Trim() != "null")
                    {
                        if (projects == null)
                        {
                            projects = new SiteClientsAllProjectsEntity();
                            projects.Create();
                            projects.F_ClientId = clientEntity.F_Id;
                            projects.F_ProjectName = entity.F_ProjectId;
                            db.Insert<SiteClientsAllProjectsEntity>(projects);
                            List<SiteClientsAllProjectsEntity> projectsList = CacheFactory.Cache().Get("XMZXX") as List<SiteClientsAllProjectsEntity>;
                            if (projectsList == null)
                            {
                                projectsList = new List<SiteClientsAllProjectsEntity>();
                            }
                            projectsList.Add(projects);
                            CacheFactory.Cache().Insert("XMZXX", projectsList);

                            SiteClientsAllProjectsMappingEntity projectsMapping = new SiteClientsAllProjectsMappingEntity();
                            projectsMapping.Create();
                            projectsMapping.F_ProjectId = projects.F_Id;
                            projectsMapping.F_DemandId = demandEntity.F_Id;
                            db.Insert<SiteClientsAllProjectsMappingEntity>(projectsMapping);
                        }
                        else
                        {
                            projects.F_ProjectName = entity.F_ProjectName;
                            db.Update<SiteClientsAllProjectsEntity>(projects);

                            SiteClientsAllProjectsMappingEntity projectsMapping = GetSiteClientsAllProjectsMapping(projects.F_Id, demandEntity.F_Id);
                            if (projectsMapping == null)
                            {
                                projectsMapping = new SiteClientsAllProjectsMappingEntity();
                                projectsMapping.Create();
                                projectsMapping.F_ProjectId = projects.F_Id;
                                projectsMapping.F_DemandId = demandEntity.F_Id;
                                db.Insert<SiteClientsAllProjectsMappingEntity>(projectsMapping);
                            }
                        }
                    }

                    ////联系人信息
                    //string strSql6 = @"select * from Site_Clients_ProjectByContact where f_id='" + entity.F_ContactId + "'";
                    SiteClientsProjectByContactEntity contacts = contactRepository.FindEntity(entity.F_ContactId);
                    if (entity.F_Contact != null && entity.F_ContactId != "null")
                    {
                        if (contacts == null)
                        {
                            contacts = new SiteClientsProjectByContactEntity();
                            contacts.Create();
                            contacts.F_Contact = entity.F_Contact;
                            contacts.F_DepartmentId = entity.F_DepartmentId;
                            contacts.F_PositionId = entity.F_PositionId;
                            contacts.F_Sex = entity.F_Sex;
                            contacts.F_Phone = entity.F_Phone;
                            contacts.F_Telephone = entity.F_Telephone;
                            contacts.F_Mail = entity.F_Mail;
                            contacts.F_ClientId = clientEntity.F_Id;
                            db.Insert<SiteClientsProjectByContactEntity>(contacts);
                            List<SiteClientsProjectByContactEntity> contactsList = CacheFactory.Cache().Get("LXRXX") as List<SiteClientsProjectByContactEntity>;
                            if (contactsList == null)
                            {
                                contactsList = new List<SiteClientsProjectByContactEntity>();
                            }
                            contactsList.Add(contacts);
                            CacheFactory.Cache().Insert("LXRXX", contacts);

                            SiteClientsContactMappingEntity contactMapping = new SiteClientsContactMappingEntity();
                            contactMapping.Create();
                            contactMapping.F_ContactId = contacts.F_Id;
                            contactMapping.F_DemandId = demandEntity.F_Id;
                            db.Insert<SiteClientsContactMappingEntity>(contactMapping);
                        }
                        else
                        {
                            contacts.F_Contact = entity.F_Contact;
                            contacts.F_DepartmentId = entity.F_DepartmentId;
                            contacts.F_PositionId = entity.F_PositionId;
                            contacts.F_Sex = entity.F_Sex;
                            contacts.F_Phone = entity.F_Phone;
                            contacts.F_Telephone = entity.F_Telephone;
                            contacts.F_Mail = entity.F_Mail;
                            contacts.F_ClientId = entity.F_ClientId;
                            db.Update<SiteClientsProjectByContactEntity>(contacts);

                            SiteClientsContactMappingEntity contactMapping = GetSiteClientsContactMapping(contacts.F_Id, demandEntity.F_Id);
                            if (contactMapping == null)
                            {
                                contactMapping = new SiteClientsContactMappingEntity();
                                contactMapping.Create();
                                contactMapping.F_ContactId = contacts.F_Id;
                                contactMapping.F_DemandId = demandEntity.F_Id;
                                db.Insert<SiteClientsContactMappingEntity>(contactMapping);
                            }
                        }
                    }

                    //string strSql7 = @"select * from Site_Client_Order where f_id='" + entity.F_OrderId + "'";
                    order = orderRepository.FindEntity(entity.F_OrderId);
                    if (order != null)
                    {
                        order.F_WorkFormId = "lc1001";
                        order.F_State = 1;
                        order.F_OrderEnvironment = 1;
                        if (demandEntity.F_Source == "1")
                        {
                            // 1:场地推荐(场地档期审核) 初始化为 -1 等待跟进
                            if (order.F_FlowUserId == null)
                            {
                                order.F_OrderState = 1;
                                order.F_OrderStateProcess = -1;
                                order.F_FlowUserId = demandEntity.F_CreatorUserId;
                            }
                        }
                        else
                        {
                            if (order.F_FlowUserId == null)
                            {
                                // 0:待指派初始化为 0 等待确认                            
                                order.F_OrderState = 0;
                                order.F_OrderStateProcess = 0;
                            }
                        }
                        if (entity.F_State == 2)
                        {
                            order.F_State = 0;
                        }
                        db.Update<ClientOrderEntity>(order);
                    }
                    else
                    {
                        order = new ClientOrderEntity();
                        order.CreateNo();
                        order.F_Id = autoApp.CreateNumber();
                        order.F_WorkFormId = "lc1001";
                        order.F_State = 1;
                        order.F_OrderEnvironment = 1;
                        order.F_DemandId = demandEntity.F_Id;
                        if (demandEntity.F_Source == "1")
                        {
                            // 1:场地推荐(场地档期审核) 初始化为 -1 等待跟进
                            order.F_OrderState = 1;
                            order.F_OrderStateProcess = -1;
                            order.F_FlowUserId = demandEntity.F_CreatorUserId;
                        }
                        else
                        {
                            // 0:待指派初始化为 0 等待确认
                            order.F_OrderState = 0;
                            order.F_OrderStateProcess = 0;
                        }
                        if (entity.F_State == 2)
                        {
                            order.F_State = 0;
                        }
                        db.Insert<ClientOrderEntity>(order);
                    }
                    db.Commit();
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return order;
        }

        /// <summary>
        /// 网站需求订单逻辑
        /// </summary>
        /// <param name="SiteClientsEntity">客户资料</param>
        /// <param name="SiteClientsProjectByContactEntity">客户客户联系人</param>
        /// <param name="SiteClientsProjectByDemandEntity">客户需求表</param>
        public Tuple<string> SubmitDemandFromWebSite(SiteClientsEntity entity1, SiteClientsProjectByContactEntity entity2, SiteClientsProjectByDemandEntity entity3)
        {
            var result = false;
            using (var db = new RepositoryBase().BeginTrans())
            {
                try
                {
                    #region 1. 添加客户公司资料
                    // Site_Clients
                    var clientsEntity = db.IQueryable<SiteClientsEntity>(p => p.F_ClientName == entity1.F_ClientName).FirstOrDefault();
                    // 公司名称为空时，每次都添加一条新的公司记录 2018/05/04 王琪提
                    // 如果联系人相关的客户资料没有，就添加
                    if (entity1.F_ClientName.IsNullOrEmpty() || clientsEntity.IsEmpty())
                    {
                        clientsEntity = new SiteClientsEntity
                        {
                            F_ClientName = entity1.F_ClientName
                        };
                        clientsEntity.Create();
                        db.Insert<SiteClientsEntity>(clientsEntity);
                    }
                    #endregion

                    #region 2. 添加客户资料
                    // Site_Clients_ProjectByContact
                    // 联系人只根据手机号码来区别是否为同一用户 2018/05/04 王琪提
                    //var clientsProjectByContactEntity = db.IQueryable<SiteClientsProjectByContactEntity>(p => p.F_Contact == entity2.F_Contact && p.F_Phone == entity2.F_Phone).FirstOrDefault();
                    var clientsProjectByContactEntity = db.IQueryable<SiteClientsProjectByContactEntity>(p => p.F_Phone == entity2.F_Phone).FirstOrDefault();
                    // 判断是否有相关客户联系人
                    if (clientsProjectByContactEntity.IsEmpty())
                    {
                        // 添加客户联系人
                        clientsProjectByContactEntity = new SiteClientsProjectByContactEntity
                        {
                            F_ClientId = clientsEntity.F_Id,
                            F_Contact = entity2.F_Contact ?? "",
                            F_Phone = entity2.F_Phone
                        };
                        clientsProjectByContactEntity.Create();
                        db.Insert<SiteClientsProjectByContactEntity>(clientsProjectByContactEntity);
                    }
                    else if (clientsProjectByContactEntity.F_ClientId != clientsEntity.F_Id)
                    {
                        clientsProjectByContactEntity.F_ClientId = clientsEntity.F_Id;
                        clientsProjectByContactEntity.Modify(clientsProjectByContactEntity.F_Id);
                        db.Update<SiteClientsProjectByContactEntity>(clientsProjectByContactEntity);
                    }

                    #endregion

                    #region 3. 添加客户需求和客户与需求映射表的关系
                    // Site_Clients_ProjectByDemand
                    // Site_Clients_ContactMapping
                    // 添加客户需求表
                    //entity.F_ActivityTheme = "";
                    //entity.F_Mating = "";
                    //entity.F_Intention = "";
                    //entity.F_Note = "";
                    //entity.F_Mark = "";
                    //entity.F_CloseReason = null;
                    entity3.F_State = 0;
                    entity3.F_Source = entity3.F_Source.IsNullOrEmpty() ? "2" : entity3.F_Source;  //"2"; // 网络用户
                    entity3.F_ClientId = clientsEntity.F_Id;
                    if (!entity3.F_Intention.IsNullOrEmpty())
                    {
                        entity3.F_Intention = entity3.F_Intention.Replace1("•", ".");
                    }
                    entity3.Create();
                    db.Insert<SiteClientsProjectByDemandEntity>(entity3);

                    // 添加客户联系人与需求映射表
                    var clientsContactMappingEntity = new SiteClientsContactMappingEntity
                    {
                        F_ContactId = clientsProjectByContactEntity.F_Id,
                        F_DemandId = entity3.F_Id
                    };
                    clientsContactMappingEntity.Create();
                    db.Insert<SiteClientsContactMappingEntity>(clientsContactMappingEntity);
                    #endregion

                    db.Commit();
                    result = true;

                    return new Tuple<string>(clientsContactMappingEntity.F_DemandId);

                }
                catch (Exception ex)
                {
                    logger.Error($"添加网站用户数据失败！原因：${ex}");
                }
            }

            return new Tuple<string>("");
        }

        public List<SelectListItemViewModel> GetSiteClientItems(string searchInput)
        {
            var result = new List<SelectListItemViewModel>();
            if (!searchInput.IsNullOrEmpty())
            {
                var strSql = $"SELECT SC.F_Id Value, SC.F_ClientName Text FROM Site_Clients SC WHERE SC.F_ClientName <> '' AND SC.F_ClientName LIKE '%{ searchInput }%' ORDER BY SC.F_CreatorTime DESC;";
                using (var db = new RepositoryBase())
                {
                    result = db.FindList<SelectListItemViewModel>(strSql);
                }
            }

            return result;

        }

    }
}
